﻿using System;
using System.ComponentModel;
using System.Data;
using System.Data.OracleClient;
using System.Web.Services;
using System.Xml;

namespace WS_Catering
{
    /// <summary>
    /// Summary description for Service1
    /// </summary>
    [WebService(Namespace = "http://vecirek.char-t.com/xml/catering/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
        // [System.Web.Script.Services.ScriptService]
    public class Service1 : WebService
    {
        private static OracleConnection Connection;

        [WebMethod]
        public string HelloWorld()
        {
            return "\n[Catering] Hello World!";
        }

        private static string GetConnectionString()
        {
          return "User ID=hejlt6am;Password=heslo;Unicode=True;Persist Security Info=True;" +
                 "Data Source=(DESCRIPTION=" +
                 "(ADDRESS=(PROTOCOL=TCP)(HOST=tirpitz.ms.mff.cuni.cz)(PORT=1511))" +
                 "(CONNECT_DATA=(SID=xml)));"; // zmena portu z 1521, ten funguje jen z ms sítě
        }

        [WebMethod]
        public XmlElement getSuitableCatering(string styl, int pocetOsob)
        {
          return getFirmaFromDB(styl, pocetOsob).DocumentElement;
        }

        /// <summary>
        /// Vrati XmlDocument obsahujuci <list><budova>...</budova>*</list>, nacitane z db
        /// </summary>
        public XmlDocument getFirmaFromDB(string styl, int pocetOsob)
        {
          string output;
          bool shouldDisconnect = !IsConnected;
          var doc = new XmlDocument();

          if (IsConnected || Connect())
          {
            try
            {
              OracleCommand command = Connection.CreateCommand();
              command.CommandType = CommandType.Text;
              command.CommandText = "SELECT ID, SYS.XMLTYPE.GETSTRINGVAL(FIRMA) FROM CATERING WHERE existsNode(firma, '/firma/styly/styl=\"" + styl + "\"') = 1";

              OracleDataReader ord = command.ExecuteReader();
              output = "<list>";
              while (ord.Read())
              {
                output += string.Format("<record><id>{0}</id>{1}</record>", ord.GetInt32(0), ord.GetString(1));
              }
              output += "</list>";
              doc.LoadXml(output);
              return doc;
            }
            catch (Exception e)
            {
              return null;
            }
            finally
            {
              if (shouldDisconnect) Disconnect();
            }
          }
          return null;
        }


        #region connect/dc

        public static bool IsConnected
        {
          get
          {
            return (Connection != null) &&
                   (Connection.State != ConnectionState.Broken) &&
                   (Connection.State != ConnectionState.Closed);
          }
        }

        public static bool Connect()
        {
          Connection = new OracleConnection { ConnectionString = GetConnectionString() };

          try
          {
            Connection.Open();
            return true;
          }
          catch (Exception e)
          {
            return false;
          }
        }

        public static bool Disconnect()
        {
          if (Connection != null)
          {
            try
            {
              if ((Connection.State != ConnectionState.Closed) &&
                  (Connection.State != ConnectionState.Broken))
              {
                Connection.Close();
              }

              Connection = null;
              return true;
            }
            catch (Exception e)
            {
              return false;
            }
          }
          return true;
        }

        #endregion
    }
}